【转】SQL Server一个字段串拆分成多行显示或者多行数据合并成一个字符串（STRING |
您所在的位置：网站首页 › sql 拆分行 › 【转】SQL Server一个字段串拆分成多行显示或者多行数据合并成一个字符串（STRING |
【转】SQL Server一个字段串拆分成多行显示或者多行数据合并成一个字符串（STRING
|
目录 概述 一、多行数据合并成一个字符串 1、通过 FOR xml path('') 合并字符串记录 2、MS SQL Server的2017新增了STRING_AGG()是一个聚合函数 二、一个字符串拆分成多行 1、拆一列数据： 2、拆多列数据： 3、创建自定义拆分函数 4、SQL Server 2016新增了string_split函数概述 STRING_AGG（合并）：多行数据合并成一个字符串，以逗号隔开。 STRING_SPLIT（拆分）:一个字符串，拆分成多行。
根据name字段，合并code declare @table1 table ( id int ,code varchar(10) , name varchar(20) ); insert into @table1 ( id,code, name ) values ( 1, 'm1','a' ), ( 2, 'm2',null ), ( 3, 'm3', 'c' ), ( 4, 'm2','d' ), ( 5, 'm1','c' ); select * from @table1; select name, files=stuff((select ','+convert(varchar, code) from @table1 b where a.name=b.name for xml path('')), 1, 1, '') from @table1 a group by name;结果：
它将由指定的分隔符分隔将字符串行连接成一个字符串。 它不会在结果字符串的末尾添加分隔符。 SELECT name, string_agg(code,';') files FROM @table1 GROUP BY name; 二、一个字符串拆分成多行 1、拆一列数据：将如下从Excel复制的一栏数据，插入到表中行进显示（同时去掉回车换行符，空白和Tab符号）： 1、利用XML解析方式(推荐) declare @moulds varchar(4000); set @moulds='55-480730-03, 55-487780-01, , 55-487780-02 '; declare @table1 table(col1 nvarchar(4000)); declare @table2 table(col1 nvarchar(40),xmlval1 xml); insert into @table1 values(replace(@moulds, char(13)+char(10), '')); select * from @table1 insert into @table2 select rtrim(ltrim(replace(bs.v1, char(9), '') )),a.xmlval1 from (select convert(xml, ''+replace(replace(col1, '，', ','), ',', '')+'') as xmlval1 from @table1) a cross apply(select k.n.value('.', 'nvarchar(80)') v1 from a.xmlval1.nodes('n') k(n) ) bs where bs.v1 !=''; select * from @table2;结果：
2、利用字符串拆解 declare @moulds varchar(4000); set @moulds='55-480730-03, 55-487780-01, , 55-487780-02 '; declare @table1 table(col1 nvarchar(4000)); declare @table2 table(col1 nvarchar(40), pos int); insert into @table1 values(replace(@moulds, char(13)+char(10), '')); select * from @table1; insert into @table2 select rtrim(ltrim(replace(substring(A.col1, B.number, charindex(',', A.col1+',', B.number)-B.number) , char(9), '') )) as col2, B.number from @table1 A inner join master..spt_values B on charindex(',', ','+A.col1, B.number)=B.number where B.type='P'; select * from @table2;结果：
有如下数据表
需求就是将Col1，Col2按照特定的字符串分割成多行
先将该字段值统一替换为逗号分割，再将逗号分割替换转为XML数据类型，再利用xml转为多个行 declare @table1 table ( ID int , Col1 nvarchar(50) , Col2 nvarchar(50) ); insert into @table1 values ( 1, 'a,b,c', '诶，必，塞，地，伊' ); insert into @table1 values ( 2, 'w', N'三四，不知道咧' ); --方式一 select a.ID, a.Col1, a.Col2, v1, v2 from ( select ID, Col1, Col2, convert(xml, '' + replace(replace(Col1, '，', ','), ',', '') + '') as xmlval1 , convert(xml, '' + replace(replace(Col2, '，', ','), ',', '') + '') as xmlval2 from @table1 ) a cross apply ( select k.n.value('.', 'nvarchar(80)') v1 from a.xmlval1.nodes('n') k(n) ) bs cross apply ( select k.n.value('.', 'nvarchar(80)') v2 from a.xmlval2.nodes('n') k(n) ) ns; --方式二 select ID, t.Col1,t.Col2, v1, v2 from @table1 as t cross apply ( values (convert(xml, '' + replace(replace(Col1, '，', ','), ',', '')+ ''), convert(xml, '' + replace(replace(Col2, '，', ','), ',', '')+ '')) ) a (xmlval1 , xmlval2 ) cross apply ( select k.n.value('.', 'varchar(80)') as v1 from a.xmlval1.nodes('n') k(n)) bs cross apply ( select k.n.value('.', 'varchar(80)') as v2 from a.xmlval2.nodes('n') k(n) ) ns; 3、创建自定义拆分函数函数功能：切分字符串， 返回一个列名为id的表 --1. 创建fn_Split函数 IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('fn_Split') AND (TYPE = 'FN' OR TYPE = 'TF' OR TYPE = 'IF') ) DROP FUNCTION fn_Split GO CREATE FUNCTION [dbo].[fn_Split] ( @str VARCHAR(MAX), @separator VARCHAR(10) ) RETURNS TABLE AS RETURN ( SELECT B.id FROM ( ( --A 的作用只是生成 'abdc' 的XML格式的数据, 提供数据源 SELECT [value] = CONVERT(XML, '' + REPLACE(@str, @separator, '') + '') ) A OUTER APPLY ( --B 的作用是将A中的 XML 数据的值枚举出来转换成行 SELECT id = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/v') N(v) ) B ) ) GO使用函数 SELECT id FROM fn_Split('a,b,d,c',',') declare @moulds varchar(4000); set @moulds='55-480730-03, 55-487780-01, , 55-487780-02 '; declare @table1 table(id INT,col1 nvarchar(MAX)); INSERT INTO @table1 VALUES(1,replace(@moulds, char(13)+char(10), '')) INSERT INTO @table1 VALUES(2,replace(@moulds, char(13)+char(10), '')) select * from @table1; SELECT a.id,rtrim(ltrim(replace(b.id, char(10), '') )) AS item FROM @table1 a CROSS APPLY dbo.fn_Split(a.col1,',') AS b where b.id !='' 4、SQL Server 2016新增了string_split函数专门用来拆分字符串。 SELECT t.id, t.name, t.description, v.value FROM test t CROSS APPLY STRING_SPLIT(t.description, ',')v;https://blog.csdn.net/sqlserverdiscovery/article/details/79187307 https://www.cnblogs.com/springsnow/p/10289104.html |
【本文地址】
今日新闻 |
推荐新闻 |